#!/user/bin/perl
use DBI;
use Carp;
use strict;
use warnings;

#-------------------------------------------------------------------------------
#  VARIABLES
#-------------------------------------------------------------------------------

# internal
my $mspacman_dir = "$ENV{'HOME'}/.mspacman";

# SQLite
# my $db               = "$mspacman_dir/mspacman_tmp.db";
my $db               = "mspacman.db";
my $dbh              = '';
my $persistent_table = 'persistent';
my $aur_table        = 'aur';
my $pacman_table     = 'pacman';

# aur specific vars
my @alphabet = ( "a" .. "z" );
my @cower_parameters =
  ( "--format '%n|²|%i|²|%c|²|%v|²|%l|²|%d|²|%a|²|%m|²|%o|²|%p|²|%s|²|%t|²|%u'", "-s" );
my %seen;

# stat vars
my $starttime = time;


#  ==>   'id',        'name',    'aurNumber',   'category',
#        'version',   'license', 'description', 'lastMod',
#        'packager',  'votes',   'aurPage',     'subDate',
#        'outOfDate', 'projectUrl'

#-------------------------------------------------------------------------------
#  mspacman
#-------------------------------------------------------------------------------

# if we cant find th database assume first run
unless ( -e $db ) {
    createDatabase();
}
 # init sqlite
 $dbh =
   DBI->connect( "dbi:SQLite:$db", "", "",
     { RaiseError => 1, PrintError => 1, AutoCommit => 0 },
   ) or croak $DBI::errstr;
 # restore seen hash
 my $query = $dbh->selectall_arrayref(qq( SELECT aurNumber FROM
     $aur_table));
 foreach (@$query) {
     foreach my $i ( 0 .. $#$_ ) {
         $seen{ $_->[$i] } = '1';
     }
 }
# get start vars and set ties

queryControl();

sub queryControl {

    # fetch start position
    my $sql = qq(select * from $persistent_table order by rowid desc limit 1);
    my $sth = $dbh->prepare($sql);
    $sth->execute;
    my @result             = $sth->fetchrow_array();
    my $first_char_commit  = 0;
    my $middle_char_commit = 0;
    my $last_char_commit   = 0;

    # generate new search query
    foreach my $last ( 0 .. 25 ) {
        foreach my $middle ( 0 .. 25 ) {
            foreach my $first ( 0 .. 25 ) {
                my $query =
                  "$alphabet[$last]$alphabet[$middle]$alphabet[$first]";
                  searchCower($query, $first, $middle, $last);
            }
        }
    }
}

my @pruned = ();
sub searchCower {
    my ($query, $f, $m, $l) = @_;
    my @cowerargs = @cower_parameters;
    push(@cowerargs, $query);
    my @result = ();
    my $sql_pers = qq(insert into $persistent_table
    (first,middle,last,rawResults) values ( ?, ?, ?, ? ));
    open(COWER, "cower @cowerargs |") or croak "msPacman error! :: $!\n";
    while(<COWER>) {
        @result = map { chomp; [ split /\|²\|/ ]; } <COWER>;
    }
    close (COWER);
#    my $nr_results = $#result;
    # iterate over results
    my $siz = $#result;
   for my $i ( 0 .. $#result ) {
        my $key = $result[$i][0];
        unless ( $seen{$key} ) {
            $seen{$key} = 1;
            @pruned = (@pruned, $result[$i])            
        }
       
    }

    my $see = keys %seen;
    my $pru = $#pruned; 
    if ($pru > 5000) {
          insertResults(@pruned);
        @pruned = ();      # do nothing    
    }
    
    if ($see > 35000) {
         insertResults(@pruned);
        @pruned = ();      # do nothing
    }
  
    print "PRU: $pru        Q: ''$query' T: $see \n";
}
sub insertResults {
    my @packages = ();
    my @packages = @_;
    my $sql_aur = qq(INSERT INTO $aur_table (name,aurNumber,category,version,license,description,lastMod,maintainer,votes,aurPage,subdate,outOfDate,projectUrl) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?));
    for my $i ( 0 .. $#packages) {
        my $sth = $dbh->prepare ( $sql_aur );
        $sth->execute( @{ $packages[$i] } );
    }
    $dbh->commit;
    my $pac = $#packages;
    
}
    # update persist table
#    my $sth = $dbh->prepare ( $sql_pers );
#    $sth->execute( $f, $m, $l, $nr_results );
#    my $sql = qq(select rowid from $aur_table order by rowid desc limit 1);
#    $sth = $dbh->prepare($sql);
#    $sth->execute;
#    my @qresult             = $sth->fetchrow_array(); 
#    my $statresult = $qresult[0];
#    my $statminutes = (time - $starttime) / 60;
#    my $stat_eta = ( $statminutes / $statresult ) * ( 38090 - $statresult );
#    print qq( msPacman :: AUR sync
#    $alphabet[$f]$alphabet[$m]$alphabet[$l] :: $statresult of 38090 packages synced in );
#    print int($statminutes);
#    print qq( minutes - ETA );
#    print int($stat_eta#); 
#    print qq( minutes left...\n);
                                            
                         


# invocation creates new empty database
sub createDatabase {
    $dbh =
      DBI->connect( "dbi:SQLite:$db", "", "",
        { RaiseError => 1, PrintError => 1, AutoCommit => 1 },
      ) or croak $DBI::errstr;

    # create AUR packages table
    $dbh->do(
qq(CREATE TABLE $aur_table (id INTEGER PRIMARY KEY, name TEXT, aurNumber INTEGER, category TEXT, version TEXT,
    license TEXT, description TEXT, lastMod TEXT, maintainer TEXT, votes INTEGER,
    aurPage TEXT, subDate TEXT, outOfDate TEXT, projectUrl TEXT))
    ) or croak $DBI::errstr;

    # creates table for internal persistent data
    $dbh->do(
qq(CREATE TABLE $persistent_table (id INTEGER PRIMARY KEY, first INTEGER,
    middle INTEGER, last INTEGER, rawResults INT))
    ) or croak $DBI::errstr;

    # set start values for first middle last
    $dbh->do(
        qq(INSERT INTO $persistent_table (first,middle,last) VALUES (0,0,0)));

    return;
    $dbh->disconnect;
}

# connect to our database

# tie our SQLite tables to perl hashes using Tie::DBI
#tie my %packages, "Tie::DBI",
#  {
#    db      => $dbh,
#    table   => 'packages',
#    key     => 'name',
#    CLOBBER => 1
#  };
#
#print %packages;

#tie %sql_aur_table,

=begin  BlockComment  # BlockCommentNo_1

my @a = (
    'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n',
    'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z'
);
my @cmd = (
",
    "-s "
);
my $query;
my %seen = ();
my $db   = "$ENV{'HOME'}/.mspacman/mspacmanTMP.db";
my $dbh;
my $aur_t      = "aur_t";
my $internal_t = "internal_t";
my $jC         = 0;        
my $kC         = 0;

sub new {    # {{{
    if ( -e $db ) {
        print "msPacman :: removing existing database...\n";
        unlink($db);
    }

    $dbh = DBI->connect("dbi:SQLite:$db") || croak "Cannot connect: $DBI::errstr";

    crint "msPacman :: creating private msPacman table...\n";
    $dbh->do(
        "CREATE TABLE $internal_t (j INTEGER, k INTEGER)"
    ) || croak "msPacman Error! :: cannot create table: $DBI::errstr";
    $dbh->do("INSERT INTO $internal_t (j,k) VALUES (0,0)");

    print "msPacman :: creating AUR table...\n";
    $dbh->do(
        "CREATE TABLE $aur_t (id            INTEGER PRIMARY KEY,
                                   
      ) || croak "msPacman Error! :: $DBI::errstr\n";

    print "msPacman :: new tables succesfully created...\n";
    queryControl();
    
    return;
}    # }}}

sub resume {
    $dbh = DBI->connect("dbi:SQLite:$db") || croak "Cannot connect: $DBI::errstr";

    # restore unique keys hash
   

    #   get last commis pos j k

    print $jC
    print $jC $jK

}

#
sub queryControl {    # {{{
    for my $i ( 0 .. 25 ) {
        $dbh->do("UPDATE $internal_t SET i= $i");
        for my $j ( $jC .. 25 ) {
            for my $k ( $kC .. 25 ) {
                $query = "$a[$i]$a[$j]$a[$k]";
                my $nrPkgs = keys %seen;
                print " ($a[$i])-($a[$j])-($a[$k])--($nrPkgs) ";
                push( @cmd, $query );
                searchCower();
            }
            $dbh->do("UPDATE $internal_t SET j= $j");

        }
    }

    return;
}    # }}}

sub searchCower {    # {{{
                     # (re)initialize result array
    my @result = ();


    # begin transaction for the coming batch
    # begin transaction for the coming batch
    $dbh->begin_work;

    # iterate over results
    for my $i ( 0 .. $#result ) {

        # make sure result is unique
        my $key = $result[$i][0];
        unless ( $seen{$key} ) {
            $seen{$key} = 1;
            sqlite_fun( @{ $result[$i] } );
        }
    }

    # save new entries
    $dbh->Commit;
}    # }}}

sub sqlite_fun {    # {{{
    my @curAr = @_;

    # specify placeholders
    $dbh->prepare(
"INSERT INTO $aur_t (aurNumber,lastMod,category,description,license,packager,name,votes,aurPage,subDate,outOfDate,projectUrl,version) VALUES ( ?,?,?,?,?,?,?,?,?,?,?,?,? );"
    ) || croak "\nmsPacman error! :: $DBI::errstr";

    # insert into db
    $dbh->execute(
        $curAr[0],  $curAr[1],  $curAr[2], $curAr[3], $curAr[4],
        $curAr[5],  $curAr[6],  $curAr[7], $curAr[8], $curAr[9],
        $curAr[10], $curAr[11], $curAr[12]
    ) || croak "\nmsPacman error! :: $DBI::errstr";
}    # }}}

# #
#   CONTROL

resume();

#new();

=end    BlockComment  # BlockCommentNo_1

=cut

